﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using ERMap;

namespace PhoneBook.Group
{
    public class Employee
    {
        public static DataSet list(string departmentid)
        {
            Hashtable keys = new Hashtable();
            keys["departmentid"] = departmentid;
            return ERMapHelper.getdata("b_employee", keys);
        }

        public static DataSet list(string departmentid, string boid)
        {
            Hashtable keys = new Hashtable();
            keys["departmentid"] = departmentid;
            keys["boid"] = boid;
            return ERMapHelper.getdata("b_employee", keys);
        }

        public static DataSet query(string groupid, string departmentid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select e.*,d.departmentname,d.departmentid,d.groupid,u.userid from b_employee e inner join b_department d on e.departmentid = d.departmentid ");
            sb.Append(" left outer join sys_users u on e.employeeid = u.employeeid and u.usertype = 4 and isnull(u.isdel,0)=0 ");
            sb.Append(" where d.groupid = @groupid ");
            sb.Append(" and isnull(e.isdel,0)=0 and isnull(d.isdel,0)=0 ");

            Hashtable keys = new Hashtable();
            keys["groupid"] = groupid;
            if (!string.IsNullOrWhiteSpace(departmentid))
            {
                keys["departmentid"] = departmentid;
                sb.Append(" and d.departmentid = @departmentid");
            }

            return ERMapHelper.getCustomData(sb.ToString(), keys);
        }

        public static DataSet query(string groupid, string departmentid, string boid)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select e.*,d.departmentname,d.departmentid,d.groupid,u.userid from b_employee e inner join b_department d on e.departmentid = d.departmentid ");
            //sb.Append(" inner join b_branchoffice b on e.boid = b.id ");
            sb.Append(" left outer join sys_users u on e.employeeid = u.employeeid and u.usertype = 4 and isnull(u.isdel,0)=0 ");
            sb.Append(" where d.groupid = @groupid ");
            sb.Append(" and isnull(e.isdel,0)=0 and isnull(d.isdel,0)=0 ");

            Hashtable keys = new Hashtable();
            keys["groupid"] = groupid;
            if (!string.IsNullOrWhiteSpace(departmentid))
            {
                keys["departmentid"] = departmentid;
                sb.Append(" and d.departmentid = @departmentid");
            }

            if (!string.IsNullOrWhiteSpace(boid))
            {
                keys["boid"] = boid;
                sb.Append(" and e.boid = @boid");
            }
            else
            {
                //sb.Append(" and (e.boid = null or e.boid = '')");
                sb.Append(" and e.boid is null");
            }

            return ERMapHelper.getCustomData(sb.ToString(), keys);
        }


        /// <summary>
        /// 查询该用户(编号为employeeid)可查看的通讯录信息
        /// </summary>
        /// <param name="employeeid"></param>
        /// <returns></returns>
        public static DataSet queryMyEmployee(string employeeid)
        {
            StringBuilder sb = new StringBuilder();
            //--号码查询
            
//declare @memberid bigint
//set @memberid = 3

            sb.Append("select e.* from b_pbmember m inner join b_employee e on m.memberid = e.employeeid and m.righttype = 1 ");
sb.Append("where m.phonebookid in  ");
	//--下面的子查询是查出可以查看哪些通讯录，包括个人可以查看哪些通讯录和部门可以查看哪些通讯录
	sb.Append("(select r.phonebookid from b_pbright r  ");//--个人可以查看哪些通讯录
	sb.Append("where r.memberid = @memberid and r.righttype = 1 ");
	sb.Append("union  ");
	sb.Append("select r.phonebookid from b_pbright r inner join b_department d  ");//--部门可以查看哪些通讯录
	sb.Append("on r.memberid = d.departmentid and r.righttype = 0 ");
	sb.Append("inner join b_employee e  ");
	sb.Append("on e.departmentid = d.departmentid ");
	sb.Append("where e.employeeid = @memberid) ");
sb.Append("union  ");
sb.Append("select e.* from b_pbmember m inner join b_department d ");
sb.Append("on m.memberid = d.departmentid and m.righttype = 0 ");
sb.Append("inner join b_employee e ");
sb.Append("on e.departmentid = d.departmentid ");
sb.Append("where m.phonebookid in ");
	//--下面的子查询是查出可以查看哪些通讯录，包括个人可以查看哪些通讯录和部门可以查看哪些通讯录
	sb.Append("(select r.phonebookid from b_pbright r ");//--个人可以查看哪些通讯录
	sb.Append("where r.memberid = @memberid and r.righttype = 1 ");
	sb.Append("union ");
	sb.Append("select r.phonebookid from b_pbright r inner join b_department d "); //--部门可以查看哪些通讯录
	sb.Append("on r.memberid = d.departmentid and r.righttype = 0 ");
	sb.Append("inner join b_employee e ");
	sb.Append("on e.departmentid = d.departmentid ");
	sb.Append("where e.employeeid = @memberid) ");



            Hashtable keys = new Hashtable();
            keys["memberid"] = employeeid;
            

            return ERMapHelper.getCustomData(sb.ToString(), keys);
        }


        public static void del(string employeeid)
        {
            Hashtable keys = new Hashtable();
            keys["employeeid"] = employeeid;
            ERMapHelper.delmodel("b_employee", keys);
        }

        public static void add(Hashtable employee)
        {
            employee["employeeid"] = "";
            ERMapHelper.setmodel("b_employee", "employeeid", employee);
        }

        public static Hashtable get(string employeeid)
        {
            Hashtable keys = new Hashtable();
            keys["employeeid"] = employeeid;

            StringBuilder sb = new StringBuilder();
            sb.Append("select e.*,d.departmentname,d.groupid from b_employee e inner join b_department d on e.departmentid = d.departmentid ");
            sb.Append(" where e.employeeid = @employeeid ");
            sb.Append(" and isnull(e.isdel,0)=0 and isnull(d.isdel,0)=0 ");

            return ERMapHelper.getCustomModel(sb.ToString(), keys);
        }

        public static void edit(Hashtable employee)
        {
            ERMapHelper.setmodel("b_employee", "employeeid", employee);
        }

        public static Hashtable getpercent(string groupid)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("select ");
            sb.Append("    gp.groupid,");
            sb.Append("    COUNT(employeeid) as emcount,");
            sb.Append("    gp.totalnum,");
            sb.Append("    round((convert(numeric(10,3),COUNT(employeeid)) / CONVERT(numeric(10,3),gp.totalnum)),2) as empercent");
            sb.Append(" from");
            sb.Append("    b_employee em ");
            sb.Append("inner join b_department dp");
            sb.Append("    on dp.departmentid = em.departmentid");
            sb.Append("    and isnull(dp.isdel,0) = 0");
            sb.Append(" inner join b_group gp");
            sb.Append("    on gp.groupid = dp.groupid");
            sb.Append("    and ISNULL(gp.isdel,0) = 0 ");
            sb.Append("where isnull(em.isdel,0) = 0 ");
            sb.Append(" and gp.groupid=" + groupid + "");
            sb.Append(" group by gp.groupid,gp.totalnum");

            return ERMapHelper.getCustomModel(sb.ToString(), null);
        }
    }
}
